//导入db模块
const db = require('../common/db/index')
//导入加密模块
const aes = require('../common/aes/index')

//定义账单类
class Transaction {
  // 添加记账信息方法
  add(user_id, amount, category, description, transaction_date, transaction_type) {
    return new Promise((resolve, reject) => {
      const sql = `INSERT INTO transactions (user_id, amount, category, description, transaction_date, transaction_type) 
              VALUES ('${user_id}', '${amount}', '${category}', '${description}', '${transaction_date}', '${transaction_type}')`;

      db.query(sql)
        .then(result => {
          resolve({
            code: 200,
            msg: "记账信息添加成功",
            transactionId: result.insertId // 返回新增记录的 ID
          });
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  // 获取本月支出
  getMonthlyExpenses(user_id, startDate, endDate) {
    return new Promise((resolve, reject) => {
      // 确保传入的 startDate 和 endDate 包括时间  
      const sql = `SELECT SUM(amount) AS total_expenses FROM Transactions   
                     WHERE user_id = '${user_id}'   
                     AND transaction_date BETWEEN '${startDate}' AND '${endDate}'   
                     AND transaction_type = '支出'`; // 根据 transaction_type 判断支出  

      db.query(sql)
        .then(results => {
          console.log(results);
          console.log(startDate, endDate);

          const totalExpenses = results[0].total_expenses || 0; // 如果没有记录则为0  
          resolve(totalExpenses); // 返回正数  
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  //获取本月收入
  getMonthlyIncome(user_id, startDate, endDate) {
    return new Promise((resolve, reject) => {
      // 直接在 SQL 字符串中插入变量值
      const sql = `SELECT SUM(amount) AS total_income FROM Transactions 
                   WHERE user_id = '${user_id}' 
                   AND transaction_date BETWEEN '${startDate}' AND '${endDate}' 
                   AND transaction_type = '收入'`; // 根据transaction_type判断收入

      db.query(sql)
        .then(results => {
          const totalIncome = results[0].total_income || 0; // 如果没有记录则为0
          resolve(totalIncome); // 返回正数
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  //获取近3日账单
  getRecentBills(user_id, startDate, endDate) {
    return new Promise((resolve, reject) => {
      const sql = `SELECT amount, transaction_type, transaction_date, category FROM Transactions 
                     WHERE user_id = '${user_id}' 
                     AND DATE(transaction_date) BETWEEN '${startDate}' AND '${endDate}'
                     ORDER BY transaction_date DESC`; // 按时间倒序获取所有账单

      db.query(sql)
        .then(results => {
          // 创建一个临时对象来存储结果
          const groupedResults = {};

          results.forEach(record => {
            const date = new Date(record.transaction_date);
            const year = date.getFullYear();
            const month = String(date.getMonth() + 1).padStart(2, '0'); // 月份从0开始，所以要加1
            const day = String(date.getDate()).padStart(2, '0');

            const formattedDate = `${month}月${day}日`; // 格式化日期

            // 初始化该日期的账单信息
            if (!groupedResults[formattedDate]) {
              groupedResults[formattedDate] = [];
            }

            // 将账单记录添加到对应的日期分组
            groupedResults[formattedDate].push({
              type: record.transaction_type,
              amount: parseFloat(record.amount), // 转换为数字类型
              transaction_date: record.transaction_date // 原始交易日期
            });
          });

          // 准备最终结果数组并按日期倒序
          const finalResults = Object.keys(groupedResults).reduce((acc, dateKey) => {
            // 按时间倒序对账单进行排序
            const bills = groupedResults[dateKey].sort((a, b) => new Date(b.transaction_date) - new Date(a.transaction_date));

            // 将每个日期的账单转换为正确的格式
            bills.forEach(bill => {
              acc.push({
                date: dateKey,
                type: bill.type,
                amount: bill.amount
              });
            });
            return acc;
          }, []);

          // 按日期倒序
          // finalResults.reverse();

          resolve(finalResults); // 返回格式化后的结果
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  // 获取总资产
  getTotalAssets(user_id) {
    return new Promise((resolve, reject) => {
      const sql = `SELECT SUM(amount) AS total_assets FROM Transactions 
                   WHERE user_id = ${user_id} AND transaction_type = '收入'`; // 仅计算收入

      db.query(sql)
        .then(results => {
          const totalAssets = results[0]?.total_assets || 0; // 如果没有结果，默认为 0
          resolve(totalAssets); // 返回总资产
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  // 获取总资产
  getTotalAssets(user_id) {
    return new Promise((resolve, reject) => {
      const sql = `SELECT SUM(amount) AS total_assets FROM Transactions 
                   WHERE user_id = ${user_id} AND transaction_type = '收入'`; // 仅计算收入

      db.query(sql)
        .then(results => {
          const totalAssets = results[0]?.total_assets || 0; // 如果没有结果，默认为 0
          resolve(totalAssets); // 返回总资产
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  // 获取总支出
  getTotalExpenditures(user_id) {
    return new Promise((resolve, reject) => {
      const sql = `SELECT SUM(amount) AS total_expenditures FROM Transactions 
                   WHERE user_id = ${user_id} AND transaction_type = '支出'`; // 仅计算支出

      db.query(sql)
        .then(results => {
          const totalExpenditures = results[0]?.total_expenditures || 0; // 如果没有结果，默认为 0
          resolve(totalExpenditures); // 返回总支出
        })
        .catch(err => {
          reject({
            code: -1,
            msg: "服务端发生错误",
            err
          });
        });
    });
  }

  // 获取总负债
  getTotalLiabilities(user_id) {
    return Promise.all([
      this.getTotalAssets(user_id),
      this.getTotalExpenditures(user_id)
    ]).then(([totalAssets, totalExpenditures]) => {
      return totalExpenditures > totalAssets ? totalExpenditures - totalAssets : 0.00; // 计算负债
    }).catch(err => {
      throw {
        code: -1,
        msg: "服务端发生错误",
        err
      };
    });
  }

  // 获取净资产
  getNetAssets(user_id) {
    return Promise.all([
      this.getTotalAssets(user_id),
      this.getTotalExpenditures(user_id)
    ]).then(([totalAssets, totalExpenditures]) => {
      return totalAssets - totalExpenditures; // 从总资产中减去支出部分
    }).catch(err => {
      throw {
        code: -1,
        msg: "服务端发生错误",
        err
      };
    });
  }

  // 获取用户的所有账单信息
  getBillsByUserId(user_id) {
    const sql = `SELECT transaction_date, category, transaction_type, amount FROM Transactions 
                 WHERE user_id = ${user_id}`;
    const results = db.query(sql);
    return results; // 返回查询结果
  }

  // 按日期分组账单信息
  groupBillsByDate(results) {
    const groupedBills = {};

    results.forEach(record => {
      const date = new Date(record.transaction_date);

      // 格式化为 YYYY年MM月DD日 格式
      const year = date.getFullYear();
      const month = String(date.getMonth() + 1).padStart(2, '0'); // 月份从0开始，所以要加1
      const day = String(date.getDate()).padStart(2, '0');

      const formattedDate = `${month}月${day}日`; // 用这种格式
      const dayOfWeek = date.toLocaleString('zh-CN', { weekday: 'long' }); // 获取星期几

      // 初始化该日期的账单信息
      if (!groupedBills[formattedDate]) {
        groupedBills[formattedDate] = {
          year: year,
          date: formattedDate,
          dayOfWeek: dayOfWeek,
          totalIncome: "0.00", // 初始化为字符串形式的金额
          totalExpenditures: "0.00", // 初始化为字符串形式的金额
          bills: [] // 用于存储当天的账单信息
        };
      }

      const amount = parseFloat(record.amount).toFixed(2); // 确保为保留两位小数的浮点数字符串

      // 更新收入与支出
      if (record.transaction_type === '收入') {
        groupedBills[formattedDate].totalIncome = (parseFloat(groupedBills[formattedDate].totalIncome) + parseFloat(amount)).toFixed(2); // 更新总收入
        groupedBills[formattedDate].bills.push({
          type: "收入",
          amount: amount,
          category: record.category, // 加入类别信息
          transaction_date: record.transaction_date // 原始交易日期
        });
      } else if (record.transaction_type === '支出') {
        groupedBills[formattedDate].totalExpenditures = (parseFloat(groupedBills[formattedDate].totalExpenditures) + parseFloat(amount)).toFixed(2); // 更新总支出
        groupedBills[formattedDate].bills.push({
          type: "支出",
          amount: amount,
          category: record.category, // 加入类别信息
          transaction_date: record.transaction_date // 原始交易日期
        });
      }
    });

    // 对每组账单进行排序（按日期倒序）
    Object.values(groupedBills).forEach(group => {
      group.bills.sort((a, b) => new Date(b.transaction_date) - new Date(a.transaction_date)); // 按时间倒序
    });

    // 转换成数组格式并返回
    return Object.values(groupedBills).sort((a, b) => new Date(b.date) - new Date(a.date)); // 按日期倒序
  }

  // 获取各类别支出总和
  getExpensesByCategory(user_id) {
    return new Promise(async (resolve, reject) => {
      try {
        // 获取支出类别的唯一列表
        const allCategoriesSql = `
                SELECT DISTINCT category FROM Transactions
                WHERE transaction_type = '支出'`;
        const allCategoriesResults = await db.query(allCategoriesSql);
        const allCategories = allCategoriesResults.map(record => record.category);

        // 获取当前月份各支出类别的支出总和
        const expensesSql = `SELECT category, SUM(amount) AS totalExpenses
                                 FROM Transactions
                                 WHERE user_id = ${user_id} 
                                   AND transaction_type = '支出' 
                                   AND MONTH(transaction_date) = MONTH(CURRENT_DATE) 
                                   AND YEAR(transaction_date) = YEAR(CURRENT_DATE)
                                 GROUP BY category`;

        const expensesResults = await db.query(expensesSql); // 直接执行查询

        // 将支出结果转换为字典以方便查找
        const expensesMap = {};
        expensesResults.forEach(record => {
          expensesMap[record.category] = parseFloat(record.totalExpenses);
        });

        // 构建最终结果，包括所有支出类别
        const formattedResults = allCategories.map(category => ({
          category: category,
          totalExpenses: expensesMap[category] || 0 // 没有支出则为0
        }));

        // 将 "其它" 类别的数据放到最后
        const otherCategory = formattedResults.find(item => item.category === "其他");
        const filteredResults = formattedResults.filter(item => item.category !== "其他");

        // 确保 "其它" 类别在最后
        resolve([...filteredResults, otherCategory ? otherCategory : { category: "其他", totalExpenses: 0 }]);
      } catch (err) {
        reject({
          code: -1,
          msg: "服务端发生错误",
          err
        });
      }
    });
  }


}


//把这个类挂在模块系统上
module.exports = Transaction